package beans;

import java.sql.*;
import java.util.*;

/** Saves an order in the database
 * 
 * @author 
 */

public class OrderBean  {

  private Connection con;
  private PreparedStatement orderPstmt;
  private PreparedStatement orderItemPstmt;
  private PreparedStatement prest;
  private PreparedStatement prestCheck;
  private PreparedStatement stmt = null;
  private ResultSet rs=null;

  private String url;
  private ShoppingBean sb;
  private String buyerName;
  private String shippingAddress;
  private String shippingZipcode;
  private String shippingCity;

    private static String orderSQL;
    private static String orderItemSQL;
    private static String updateTablesSQL;
    private static String verifyTablesSQL;

  public OrderBean(String _url, ShoppingBean _sb, String _buyerName, 
		   String _shippingAddress, String _shippingZipcode, 
		   String _shippingCity){
    url = _url;
    sb = _sb;
    buyerName=_buyerName;
    shippingAddress=_shippingAddress;
    shippingZipcode=_shippingZipcode;
    shippingCity=_shippingCity;
  }

  /**
   * Saves an order in the database
   * 
   */
  public void saveOrder() throws Exception{
      
    orderSQL="INSERT INTO ORDERS(BUYER_NAME,";
    orderSQL += " SHIPPING_ADRESS, SHIPPING_ZIPCODE, SHIPPING_CITY)";
    orderSQL += " VALUES(?,?,?,?)";
    try{

	// load the driver and get a connection

	Class.forName("com.mysql.jdbc.Driver");
	con = DriverManager.getConnection(url);
        
        con.setAutoCommit(false);
        
	// turn off autocommit to handle transactions yourself
	
	orderPstmt = con.prepareStatement(orderSQL);
	orderPstmt.setString(1, buyerName);
	orderPstmt.setString(2, shippingAddress);
	orderPstmt.setString(3, shippingZipcode);
	orderPstmt.setString(4, shippingCity);
	orderPstmt.execute();

	// now handle all items in the cart

	saveOrderItems();
	sb.clear();
	con.commit();  // end the transaction
    }
    catch(Exception e){
	try{
	    con.rollback();    // failed, rollback the database
	}
	catch(Exception ee){}
	throw e;
    }
    finally{
	try {
	    rs.close();
	}
	catch(Exception e) {}       
	try {
	    stmt.close();
	}
	catch(Exception e) {}
	try{
	    orderPstmt.close();
	}
	catch(Exception e){}
	try{
	    orderItemPstmt.close();
	}
	catch(Exception e){}
        try{
            prest.close();
	}
	catch(Exception e){}
	try{
	    con.close();
	}
	catch(Exception e){}
    }
  }

/**
 * Saves the different items in the order
 */
  private void saveOrderItems() throws Exception{

      // Deduct number of elements from components tables
      verifyTablesSQL  = "SELECT HDD.COUNT, ";
      verifyTablesSQL += "MOTHERBOARD.COUNT, ";
      verifyTablesSQL += "PROCESSOR.COUNT, ";
      verifyTablesSQL += "RAM.COUNT, ";
      verifyTablesSQL += "VIDEOCARD.COUNT ";
      verifyTablesSQL += "FROM HDD, MOTHERBOARD, PROCESSOR, RAM, VIDEOCARD ";
      verifyTablesSQL += "WHERE HDD.ID_HDD =  ? ";
      verifyTablesSQL += "AND MOTHERBOARD.ID_MB = ? ";
      verifyTablesSQL += "AND PROCESSOR.ID_PROC = ? ";
      verifyTablesSQL += "AND RAM.ID_RAM = ? ";
      verifyTablesSQL += "AND VIDEOCARD.ID_VC = ? ";
      prestCheck = con.prepareStatement(verifyTablesSQL);
      
      // get the value of the last stored AUTO_INCREMENT variable
      // i. e. ORDER_ID

      orderItemSQL="INSERT INTO ORDER_ITEMS(ORDER_ID, ";
      orderItemSQL += "RAM_ID, HDD_ID, PROC_ID, VC_ID, MB_ID, ";
      orderItemSQL += "QUANTITY, PRICE) VALUES (?,?,?,?,?,?,?,?)";
      stmt = con.prepareStatement("SELECT LAST_INSERT_ID()");
      rs = stmt.executeQuery();
      rs.next();
      int orderId=rs.getInt(1);

      Iterator iter = ((Collection)sb.getCart()).iterator();
      HDDBean hddBean = null;
      MotherboardBean motherboardBean = null;
      ProcessorBean processorBean = null;
      RAMBean ramBean = null;
      VideocardBean videocardBean = null;
      Object tmpArr[];

      //Loop over the entire collection, i.e the entire shopping cart
 
      orderItemPstmt = con.prepareStatement(orderItemSQL);
      
      // Deduct number of elements from component tables
      updateTablesSQL =  "UPDATE HDD, MOTHERBOARD, PROCESSOR, RAM, VIDEOCARD ";
      updateTablesSQL += "SET HDD.COUNT = HDD.COUNT - ?, ";
      updateTablesSQL += "MOTHERBOARD.COUNT = MOTHERBOARD.COUNT - ?, ";
      updateTablesSQL += "PROCESSOR.COUNT = PROCESSOR.COUNT - ?, ";
      updateTablesSQL += "RAM.COUNT = RAM.COUNT - ?, ";
      updateTablesSQL += "VIDEOCARD.COUNT = VIDEOCARD.COUNT - ? ";
      updateTablesSQL += "WHERE HDD.ID_HDD =  ? ";
      updateTablesSQL += "AND MOTHERBOARD.ID_MB = ? ";
      updateTablesSQL += "AND PROCESSOR.ID_PROC = ? ";
      updateTablesSQL += "AND RAM.ID_RAM = ? ";
      updateTablesSQL += "AND VIDEOCARD.ID_VC = ? ";
      
      prest = con.prepareStatement(updateTablesSQL);
      
      while(iter.hasNext()){
        
	  tmpArr = (Object[])iter.next();
          
	  hddBean = (HDDBean)tmpArr[0];
          motherboardBean = (MotherboardBean)tmpArr[1];
          processorBean = (ProcessorBean)tmpArr[2];
          ramBean = (RAMBean)tmpArr[3];
          videocardBean = (VideocardBean)tmpArr[4];
          
          prestCheck = con.prepareStatement(verifyTablesSQL);
          prestCheck.setInt(1, hddBean.getId());
          prestCheck.setInt(2, motherboardBean.getId());
          prestCheck.setInt(3, processorBean.getId());
          prestCheck.setInt(4, ramBean.getId());
          prestCheck.setInt(5, videocardBean.getId());
          ResultSet executeQuery = prestCheck.executeQuery();
          while ( executeQuery.next() ) {
              if (executeQuery.getInt("HDD.COUNT")<((Integer)tmpArr[5]).intValue()){
                  throw new Exception("Hdd, which you requested is not presented in shop in requested amount");
              }
              else if (executeQuery.getInt("MOTHERBOARD.COUNT")<((Integer)tmpArr[5]).intValue()){
                  throw new Exception("Motherboard, which you requested is not presented in shop in requested amount");
              }
              else if (executeQuery.getInt("PROCESSOR.COUNT")<((Integer)tmpArr[5]).intValue()){
                  throw new Exception("Processor, which you requested is not presented in shop in requested amount");
              }
              else if (executeQuery.getInt("RAM.COUNT")<((Integer)tmpArr[5]).intValue()){
                  throw new Exception("RAM, which you requested is not presented in shop in requested amount");
              }
              else if (executeQuery.getInt("VIDEOCARD.COUNT")<((Integer)tmpArr[5]).intValue()){
                  throw new Exception("Videocard, which you requested is not presented in shop in requested amount");
              }
              else{
                 
                  orderItemPstmt.setInt(1, orderId);
                  orderItemPstmt.setInt(2, ramBean.getId());
                  orderItemPstmt.setInt(3, hddBean.getId());
                  orderItemPstmt.setInt(4, processorBean.getId());
                  orderItemPstmt.setInt(5, videocardBean.getId());
                  orderItemPstmt.setInt(6, motherboardBean.getId());     
                  orderItemPstmt.setInt(7, ((Integer)tmpArr[5]).intValue());
                  orderItemPstmt.setInt(8, ((Integer)tmpArr[6]).intValue());
                  orderItemPstmt.execute();

                  prest.setInt(1, ((Integer)tmpArr[5]).intValue());
                  prest.setInt(2, ((Integer)tmpArr[5]).intValue());
                  prest.setInt(3, ((Integer)tmpArr[5]).intValue());
                  prest.setInt(4, ((Integer)tmpArr[5]).intValue());
                  prest.setInt(5, ((Integer)tmpArr[5]).intValue());
                  prest.setInt(6, hddBean.getId());
                  prest.setInt(7, motherboardBean.getId());
                  prest.setInt(8, processorBean.getId());
                  prest.setInt(9, ramBean.getId());
                  prest.setInt(10, videocardBean.getId());
                  prest.executeUpdate();
                  
                  
              }
                  
          }
          
          
      }
      
            
  }
 
}